﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using Model;

namespace DAL
{
    public  class PositionDAL
    {
        /// <summary>
        /// 获得数据列表
        /// </summary>
        public static DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select *");
            strSql.Append(" FROM ExamDB.dbo.tb_Position ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        /// <param name="activity_id"></param>
        public static void Delete(int position_id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete ExamDB.dbo.tb_Position ");
            strSql.Append(" where position_id=@IdPosition");
            SqlParameter[] parameters = {
                                            new SqlParameter("@IdPosition",SqlDbType.Int,4)
                                        };
            parameters[0].Value = position_id;
            DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 增加一条数据
        /// </summary>
        public static int Add(Position model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into dbo.tb_Position ");
            strSql.Append("(position_name, position_description)");
            strSql.Append(" values (");
            strSql.Append("'").Append(model.Position_name).Append("'");
            strSql.Append(",").Append("'").Append(model.Position_description).Append("'").Append(")");

            return DbHelperSQL.ExecuteSql(strSql.ToString());
        }

        public static string GetPositionNmById(int positionId)
        {
            string  ret =string.Empty;
            StringBuilder strSql = new StringBuilder();

            strSql.Append(@"select position_name from dbo.tb_Position where position_id = ");
            strSql.Append("'");
            strSql.Append(positionId.ToString());
            strSql.Append("'");
            try
            {
                DataSet GUIDds = DbHelperSQL.Query(strSql.ToString());
                if (GUIDds != null && GUIDds.Tables[0].Rows.Count == 1)
                {
                    ret = GUIDds.Tables[0].Rows[0][0].ToString();
                }
                return ret;
            }
            catch
            {
             
                return ret;
            }
        }

        public static DataSet GetExportDSIncAllDim(int positionId, int dimID)
        {
            if (positionId < 0 || dimID < 0)
            {
                return null;
            }
            else
            {
                StringBuilder strSql = new StringBuilder();

                strSql.Append(@"SELECT 

                A.[TESTER_SN] AS 序列号
                ,(SELECT TESTER_NAME FROM DBO.TB_TESTER_INFO WHERE TESTER_SN=A.[TESTER_SN]) AS 姓名
                ,(SELECT tester_testendtime FROM DBO.TB_TESTER_INFO WHERE TESTER_SN=A.[TESTER_SN]) AS 测试日期
                ,(SELECT QUESTION_NO FROM DBO.TB_QUESTION WHERE QUESTION_ID=A.[QUESTION_ID]) AS 题目编号
                ,(SELECT question_title FROM DBO.TB_QUESTION WHERE QUESTION_ID=A.[QUESTION_ID]) AS 题干内容
                ,(CASE A.QUESTION_TESTER_SELECTED
                        WHEN ('A') THEN (SELECT QUESTION_ANSWER_A_SCORE FROM TB_QUESTION WHERE QUESTION_ID=A.QUESTION_ID)
                        WHEN ('B') THEN (SELECT QUESTION_ANSWER_B_SCORE FROM TB_QUESTION WHERE QUESTION_ID=A.QUESTION_ID)
                        WHEN ('C') THEN (SELECT QUESTION_ANSWER_C_SCORE FROM TB_QUESTION WHERE QUESTION_ID=A.QUESTION_ID)
                        WHEN ('D') THEN (SELECT QUESTION_ANSWER_D_SCORE FROM TB_QUESTION WHERE QUESTION_ID=A.QUESTION_ID)
                        WHEN ('E') THEN (SELECT QUESTION_ANSWER_E_SCORE FROM TB_QUESTION WHERE QUESTION_ID=A.QUESTION_ID)
                        ELSE '0' END
                        ) AS 原始得分
                ,(DATEDIFF(SECOND,A.[QUESTION_START_DT],A.[QUESTION_END_DT])) AS 实际答题时间_秒
                ,(CASE(SELECT QUESTION_STATUS FROM DBO.TB_QUESTION WHERE QUESTION_ID=A.[QUESTION_ID]) 
                        WHEN ('1') THEN '有效'
	                    ELSE '无效' END)
                AS 题目状态
                ,(SELECT ACTIVITY_NAME FROM DBO.TB_ACTIVITY WHERE ACTIVITY_ID=(SELECT ACTIVITY_ID FROM DBO.TB_GUID_NUMBER WHERE GUID_STR=A.[TESTER_SN])) AS 项目名称
                    FROM [EXAMDB].[DBO].[TB_TESTER_QUESTION_STATUS] A
                WHERE 
                A.[TESTER_SN] IN(
                SELECT GUID_STR FROM DBO.TB_GUID_NUMBER B WHERE ACTIVITY_ID IN 
                (SELECT ACTIVITY_ID FROM  DBO.TB_ACTIVITY  WHERE ACTIVITY_POSITIONID  = ");

                strSql.Append(positionId.ToString());
                strSql.Append(@"))
                AND 
                (
                (SELECT  QUESTION_DIMENSIONID FROM DBO.TB_QUESTION WHERE  [QUESTION_ID] = A.[QUESTION_ID])= ");
                strSql.Append(dimID.ToString());
                strSql.Append(")");
                return DbHelperSQL.Query(strSql.ToString());
            }
        }        
    }
}
